package vn.fithou.houelearning.delegate;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import vn.hou.fit.vnla.bean.*;
import vn.hou.fit.vnla.util.*;

public class VanbanDelegate {
	/*
	 * Lấy danh sách Vanban
	 */
	public static Vector<Vanban> getAll() throws SQLException
	{
		Vector<Vanban> lstVanban = new Vector<Vanban>();
		Connection con = UtilDelegate.getConnection();
		String sql = "select * from tblVanban";
		try{
			Statement stmt = con.createStatement();
			ResultSet rs =  stmt.executeQuery(sql);
			
			while(rs.next())
			{
				Vanban oVanban = new Vanban();
				oVanban.setPK_iVanbanID(rs.getInt("PK_iVanbanID"));
				oVanban.setFK_sCoquanbanhanhID(rs.getString("FK_sCoquanbanhanhID"));
				oVanban.setFK_sDanhmucvanbanID(rs.getString("FK_sDanhmucvanbanID"));
				oVanban.setsTenvanban(rs.getString("sTenvanban"));
				oVanban.setsSohieu(rs.getString("sSohieu"));
				oVanban.setdNgaybanhanh(rs.getDate("dNgaybanhanh"));
				oVanban.setdNgaycohieuluc(rs.getDate("dNgaycaohieuluc"));
				oVanban.setdNgayhethan(rs.getDate("dNgayhethan"));
				oVanban.setiTrangthai(rs.getInt("iTrangthai"));
				oVanban.setsGhichu(rs.getString("sGhichu"));
				lstVanban.add(oVanban);
			}
			
		}
		catch (Exception e) 
		{
			e.printStackTrace();
			lstVanban = null;
		}
		finally
		{
			UtilDelegate.closeConnection(con);	
		}
		return lstVanban;
	}
	/*
	 * Lấy danh sách Vanban
	 */
	public static Vector<Vanban> getByFK_sDanhmucvanbanID(String FK_sDanhmucvanbanID) throws SQLException
	{
		Vector<Vanban> lstVanban = new Vector<Vanban>();
		Connection con = UtilDelegate.getConnection();
		String sql = "select * from tblVanban where FK_sDanhmucvanbanID = '"+ FK_sDanhmucvanbanID +"'";
		try{
			Statement stmt = con.createStatement();
			ResultSet rs =  stmt.executeQuery(sql);
			
			while(rs.next())
			{
				Vanban oVanban = new Vanban();
				oVanban.setPK_iVanbanID(rs.getInt("PK_iVanbanID"));
				oVanban.setFK_sCoquanbanhanhID(rs.getString("FK_sCoquanbanhanhID"));
				oVanban.setFK_sDanhmucvanbanID(rs.getString("FK_sDanhmucvanbanID"));
				oVanban.setsTenvanban(rs.getString("sTenvanban"));
				oVanban.setsSohieu(rs.getString("sSohieu"));
				oVanban.setdNgaybanhanh(rs.getDate("dNgaybanhanh"));
				oVanban.setdNgaycohieuluc(rs.getDate("dNgaycaohieuluc"));
				oVanban.setdNgayhethan(rs.getDate("dNgayhethan"));
				oVanban.setiTrangthai(rs.getInt("iTrangthai"));
				oVanban.setsGhichu(rs.getString("sGhichu"));
				lstVanban.add(oVanban);
			}
			
		}
		catch (Exception e) 
		{
			e.printStackTrace();
			lstVanban = null;
		}
		finally
		{
			UtilDelegate.closeConnection(con);	
		}
		return lstVanban;
	}
	
	/*
	 * Lấy Vanban theo ID
	 */
	public static Vanban getByPK(Integer PK_iVanbanID) throws Exception
	{
		Vanban oVanban = new Vanban();
		Connection con = UtilDelegate.getConnection();
		String sql = "select * from tblVanban where PK_iVanbanID = " + PK_iVanbanID + "";
		try{
			Statement stmt = con.createStatement();
			ResultSet rs =  stmt.executeQuery(sql);
			
			if(rs.next())
			{
				oVanban.setPK_iVanbanID(rs.getInt("PK_iVanbanID"));
				oVanban.setFK_sCoquanbanhanhID(rs.getString("FK_sCoquanbanhanhID"));
				oVanban.setFK_sDanhmucvanbanID(rs.getString("FK_sDanhmucvanbanID"));
				oVanban.setsTenvanban(rs.getString("sTenvanban"));
				oVanban.setsSohieu(rs.getString("sSohieu"));
				oVanban.setdNgaybanhanh(rs.getDate("dNgaybanhanh"));
				oVanban.setdNgaycohieuluc(rs.getDate("dNgaycaohieuluc"));
				oVanban.setdNgayhethan(rs.getDate("dNgayhethan"));
				oVanban.setiTrangthai(rs.getInt("iTrangthai"));
				oVanban.setsGhichu(rs.getString("sGhichu"));
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
			oVanban = null;
		}
		finally
		{
			UtilDelegate.closeConnection(con);	
		}
		return oVanban;
	}
	/*
	 * Tìm kiếm Vanban
	 */
	public static Vector<Vanban> getBySearch(String strKey) throws SQLException
	{
		Vector<Vanban> lstVanban = new Vector<Vanban>();
		Connection con = UtilDelegate.getConnection();
		String sql = "SELECT * FROM tblVanban WHERE sTenchuong LIKE '%"+ strKey +"%' ORDER BY sTendanhmuc ASC";
		try{
			Statement stmt = con.createStatement();
			ResultSet rs =  stmt.executeQuery(sql);
			
			while(rs.next())
			{
				Vanban oVanban = new Vanban();
				oVanban.setPK_iVanbanID(rs.getInt("PK_iVanbanID"));
				oVanban.setFK_sCoquanbanhanhID(rs.getString("FK_sCoquanbanhanhID"));
				oVanban.setFK_sDanhmucvanbanID(rs.getString("FK_sDanhmucvanbanID"));
				oVanban.setsTenvanban(rs.getString("sTenvanban"));
				oVanban.setsSohieu(rs.getString("sSohieu"));
				oVanban.setdNgaybanhanh(rs.getDate("dNgaybanhanh"));
				oVanban.setdNgaycohieuluc(rs.getDate("dNgaycaohieuluc"));
				oVanban.setdNgayhethan(rs.getDate("dNgayhethan"));
				oVanban.setiTrangthai(rs.getInt("iTrangthai"));
				oVanban.setsGhichu(rs.getString("sGhichu"));
				
				lstVanban.add(oVanban);
			}
			
		}
		catch (Exception e) 
		{
			e.printStackTrace();
			lstVanban = null;
		}
		finally
		{
			UtilDelegate.closeConnection(con);	
		}
		return lstVanban;
	}
//	public static Integer Update(Vanban oVanban) throws SQLException
//	{
//		Connection con = UtilDelegate.getConnection();
//		Integer iRes = 0;
//		String sql = "UPDATE tblVanban SET sTendanhmuc = N'"+ oVanban.getsTendanhmuc() +"' WHERE PK_iDanhmucvanbanID = '"+ oVanban.getPK_iDanhmucvanbanID() +"'";
//		try 
//		{
//			Statement stmt = con.createStatement();
//			iRes = stmt.executeUpdate(sql);
//		} 
//		catch (SQLException e) 
//		{
//			e.printStackTrace();
//		}
//		finally
//		{
//			UtilDelegate.closeConnection(con);
//		}
//		return iRes;
//	}
//	
//	public static Integer Insert(Vanban oVanban) throws SQLException
//	{
//		Connection con = UtilDelegate.getConnection();
//		Integer iRes = 0;
//		String sql = "insert into tblVanban(PK_iDanhmucvanbanID, FK_iDanhmucvanbanID, iSothutu, sTenchuong) values("+ oVanban.getPK_iDanhmucvanbanID() +", "+ oVanban.getFK_iDanhmucvanbanID() +", " + oVanban.getiSothutu() + ", N'" + oVanban.getsTenchuong() + "')";
//		try 
//		{
//			Statement stmt = con.createStatement();
//			iRes = stmt.executeUpdate(sql);
//		} 
//		catch (SQLException e) 
//		{
//			e.printStackTrace();
//		}
//		finally
//		{
//			UtilDelegate.closeConnection(con);
//		}
//		return iRes;
//	}
//	
//	public static Integer Delete(String PK_iDanhmucvanbanID) throws SQLException
//	{
//		Integer iRes = 0;
//		Connection con = UtilDelegate.getConnection();
//		try
//		{
//			Statement stmt = con.createStatement();
//			iRes =  stmt.executeUpdate("DELETE FROM tblVanban WHERE PK_iDanhmucvanbanID = " + PK_iDanhmucvanbanID + "");
//		}
//		catch (SQLException e)
//		{
//			e.printStackTrace();
//		}
//		finally
//		{
//			UtilDelegate.closeConnection(con);	
//		}
//		return iRes;
//	}
}
